# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/1/20 9:28
# @Author  : qiaoc
# @File    : PyMsql.py
# @Desc    : 数据库操作
import pymysql
import time
import random
from config.ConfigEum import *
import string

class PyMysql:
    """定义数据库操作类"""
    bill_table_name = 'rpa_ele_billlist'
    rpa_table_name = 'rpa_nm_universe'

    # 稽核点比对信息表
    nf_audit_point_tablename = 'nf_audit_point'
    nf_audit_point_table_key = ['code',  # 缴费单编号
                                'name',  # 稽核点名称（稽核规则名称）
                                'report_result',  # 上报结果（页面内容）,就是从页面抓取的此项稽核规则数据
                                'check_results',  # 核对结果（附件内容）
                                'is_qualified',  # 是否合格(0-不合格,1-合格)
                                'mode',  # 稽核方式（RPA,人工），此处默认RPA
                                'compare_result',  # 比对结果，稽核结果说明
                                'affix_url',  # 附件url
                                'type',
                                # 稽核类型(\r\n报账点电费信息【0-合同信息，1-报账点缴费信息，2-其他费用，3-缴费标杆信息，4-电表缴费信息，5-附件】\r\n固化审核信息【11-基础信息,12-电费信息，13-供应商信息，14-附件\r\n】)
                                'over_because',  # 超标原因
                                ]
    # 缴费单审核表
    sys_audit_info_tablename = 'sys_audit_info'
    sys_audit_info_table_key = ['payment_code',  # 缴费单编码
                                'audit_status',  # 审核状态（通过，不通过，审核中）
                                'audit_refused_type',  # 审核不通过类型
                                'audit_rd_type',  # 审核意见
                                'is_artificial',  # 是否人工审核（0：是  1：不是）
                                'save_time',  # 录入时间，审核时间
                                ]
    def __init__(self):
        self.db = pymysql.connect(
            host= ConfigEnum.host.value,
            user= ConfigEnum.user.value,
            password= ConfigEnum.password.value,
            port= ConfigEnum.port.value,
            db= ConfigEnum.sql_db.value,
            connect_timeout=200)
        self.__cursor = self.db.cursor()

    @staticmethod
    def execute_sql(sql):
            '''
                数据库连接操作 - 操作数据库：增，删，改
                :param sql: 需要执行的SQL
                :return:
            '''
            connet = pymysql.connect(
                                host=ConfigEnum.host.value,
                                user=ConfigEnum.user.value,
                                password=ConfigEnum.password.value,
                                port=ConfigEnum.port.value,
                                db=ConfigEnum.sql_db.value,
                                connect_timeout=200)
            cursor = connet.cursor(cursor=pymysql.cursors.DictCursor)
            try:
                # print(sql)
                cursor.execute(sql)
                connet.commit()
            except Exception as e:
                pass
            finally:
                cursor.close()
                connet.close()
            pass

    @staticmethod
    def get_db_data(sql):
        '''
         查询数据库数据
        :param sql:
        :return:
        '''

        connet = pymysql.connect( # 数据库连接
            host=ConfigEnum.host.value,
            user=ConfigEnum.user.value,
            password=ConfigEnum.password.value,
            port=ConfigEnum.port.value,
            db=ConfigEnum.sql_db.value,
            connect_timeout=200)
        cursor = connet.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            cursor.execute(sql)
            return cursor.fetchall() # 获取元组数据
        except Exception as e:
            pass
        finally:
            cursor.close()
            connet.close()
        pass


    # 汇总数据insert数据库操作,keys为数组：数据库插入关键字，data_list为二维数组：插入数据库的值
    @staticmethod
    def insert_data(table, keys, data_list):
        # 数据库连接
        db = pymysql.connect(
            host=ConfigEnum.host.value,
            user=ConfigEnum.user.value,
            password=ConfigEnum.password.value,
            port=ConfigEnum.port.value,
            db=ConfigEnum.sql_db.value,
            connect_timeout=200)
        cursor = db.cursor()
        try:
            k_str = ','.join(keys)
            for values in data_list:
                v_str = str(values).replace('[', '').replace(']', '')
                sql = """INSERT INTO """ + table + """ (""" + k_str + """)
                     VALUES (""" + v_str + """)"""
                try:
                    # print(sql)
                    cursor.execute(sql)
                    # print('数据插入成功', values)
                    db.commit()
                except Exception as e:
                    print('数据插入失败:', sql, e)
                    db.rollback()
        except Exception as e:
            pass
        finally:
            cursor.close()
            db.close()

    # 查询用电信息，进行数据处理，返回列表['电价','电量','电损值']
    def select_eleinfo(self,table, key, code):  # （表名，键名，键值）
        result_list = []  # 返回的列表信息
        cursor = self.__cursor
        try:
            sql = """SELECT electricity_price, electricity_consumption, electricity_loss FROM """ \
                  + str(table) + """ WHERE """ + str(key) + """=""" + """'""" + str(code) + """' and recycle_id = '0' """
            try:
                cursor.execute(sql)
                # 使用fetchall()获取全部数据
                data = cursor.fetchall()
                # print(data)
                for info in data[0]:
                    info = info.replace('：', ':')  # 统一转成英文字符
                    if ':' in info:
                        if '用电量1' in str(info):
                            info = info.split("用电量")
                        elif '电损电量1' in str(info):
                            info = info.split("电损电量")
                        elif '电费单价1' in str(info):
                            info = info.split("电费单价")
                        now_data_list = []
                        for result in info:
                            if result != '':
                                now_data_list.append(str(result.split(":")[-1]).replace('\n', ''))
                        result_list.append(now_data_list)
                        # info = info.split(":")
                        # result_list.append(info[-1])
                    elif not info or info == 0 or info == '0':
                        info = 0
                        result_list.append(info)
                return result_list
            except Exception as e:
                # print('查询失败：', e)
                pass
        except Exception as e:
            pass
        finally:
            cursor.close()
            self.db.close()

    # 多块电表场景，进行数据处理，返回列表['电价','电量','电损值']
    @staticmethod
    def select_eleinfo1(data):
        date_list = []
        date_list.append([data['electricity_price'], data['electricity_consumption'], data['electricity_loss']])
        result_list = []  # 返回的列表信息
        try:
            for info in date_list[0]:
                info = info.replace('：', ':')  # 统一转成英文字符
                if ':' in info:
                    if '用电量1' in str(info):
                        info = info.split("用电量")
                    elif '电损电量1' in str(info):
                        info = info.split("电损电量")
                    elif '电费单价1' in str(info):
                        info = info.split("电费单价")
                    now_data_list = []
                    for result in info:
                        if result != '':
                            now_data_list.append(str(result.split(":")[-1]).replace('\n', ''))
                    result_list.append(now_data_list)
                    # info = info.split(":")
                    # result_list.append(info[-1])
                elif not info or info == 0:
                    info = 0
                    result_list.append(info)
            return result_list
        except Exception as e:
            pass

    # 判断多块电表场景：
    #   多块电表相同户号，且只有一行数据用电量和电损电量不为0场景
    #   多块电表部分相同户号，且相同户号只有一行数据用电量和电损电量不为0场景
    def select_scene(self,table, key, code, set_list):  # （表名，键名，键值，不同户号）

        whether_execute = []  # 如果满足条件，对应户号是取第几行数据
        cursor = self.__cursor
        try:
            for es_number in set_list:
                not_zero_count = 0  # 每个户号的用电量和电损电量不为0的个数
                row = 0  # 当前户号用电量和电损电量不为0的行号（0开始）
                sql = """SELECT electricity_consumption, electricity_loss FROM """ \
                      + str(table) + """ WHERE """ + str(key) + """=""" + """'""" + str(
                    code) + """' and es_number='""" + str(es_number) + """' and recycle_id = '0' """
                try:
                    cursor.execute(sql)
                    # 使用fetchall()获取全部数据
                    data = cursor.fetchall()

                    if len(data) == 1:
                        whether_execute.append([es_number, row])
                        continue
                    for i in range(len(data)):
                        result_list = []  # 返回的列表信息
                        for info in data[i]:
                            info = info.replace('：', ':')  # 统一转成英文字符
                            if ':' in info:
                                if '用电量1' in str(info):
                                    info = info.split("用电量")
                                elif '电损电量1' in str(info):
                                    info = info.split("电损电量")
                                now_data_list = []
                                for result in info:
                                    if result != '':
                                        now_data_list.append(str(result.split(":")[-1]).replace('\n', ''))
                                result_list.append(now_data_list)
                            elif not info or info == 0:
                                info = 0
                                result_list.append(info)
                        # 判断该行的用电量1和电损电量1是否为0
                        if result_list[0][0] == '0' and result_list[1][0] == '0':
                            pass
                        else:
                            not_zero_count = not_zero_count + 1
                            row = i
                    # 判断该户号只有1行用电量1和电损电量1不为0
                    if not_zero_count == 1:
                        whether_execute.append([es_number, row])
                except Exception as e:
                    pass
        except Exception as e:
            pass
        finally:
            cursor.close()
            self.db.close()
            return whether_execute


    def select_some(self, table, filed, value):
        """
        :param table:
        :param show_list type 列表 （字段）:
        :param condition type 字典:
        :return:
        """
        sql = "select * from {table} where {filed} = '{value}'".format(table=table, filed=filed, value=value)
        cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute(sql)
        result = cursor.fetchall()
        # self.close_db()
        if result:
            return result
        else:
            return None


    def transferContent(self, content):
        if content is None:
            return None
        else:
            string = ""
            for c in content:
                if c == '"':
                    string += '\\\"'
                elif c == "'":
                    string += "\\\'"
                elif c == "\\":
                    string += "\\\\"
                else:
                    string += c
            return string

    # audit_list列表，遍历【统计不通过原因】字段，然后找到对应【审核不通过类型】生成一个list存到audit_refused_info中
    @staticmethod
    def ergodic_Noresult_audit_list(audit_list):
        # print(audit_list)
        failed_result_list = []  # 需要插入audit_refused_info表的数据
        # 【金额有误】类型的原因
        type_1_list = ['【实际不含税电费金额】+【实际电费税金】+【其他费用】的【含税金额】不等于【实际报账金额】', '【实际报账金额】与【电费明细】不一致',
                       '【实际总金额】不等于【实际核销金额】与【支付金额】之和']
        # 【超标杆未分析或分析不合理】类型的原因
        type_2_list = ['请上传超标情况分析报告']
        # 【信息与明细不符】类型的原因
        type_3_list = ['报账点名称与电力清单中名称不一致', '系统中录入的分摊比例与附件不一致', '本期读数与电费明细不一致','上期读数与电费明细不一致', '电费单价与电费明细不一致', '用电量与电费明细不一致',
                       '电损电量与电费明细不一致', '缴费期始/终与电费明细不一致', '【用电量（度）】=【本期读数（度）】减去【上期读数（度）】判断不通过']
        audit_uuid  = PyMysql().generate_random_str(32)
        for result in audit_list:
            if result[4] == '0':
                code = result[0]  # 工单号
                audit_refused_type = '其他'  # 审核不通过类型
                stat_refused_type = result[6]  # 统计不通过原因
                if stat_refused_type in type_1_list:
                    audit_refused_type = '金额有误'
                elif stat_refused_type in type_2_list:
                    audit_refused_type = '超标杆未分析或分析不合理'
                elif stat_refused_type in type_3_list:
                    audit_refused_type = '信息与明细不符'
                else:
                    audit_refused_type = '其他'

                # 1. 根据code查询当前用电量，金额，以及录入人
                payment_sql = "SELECT " \
                              "actual_reimbursement_amount,actual_total_pc " \
                              "FROM  rp_payment_info " \
                              "where payment_code = '%s' and recycle_id = '0' " % code

                verification_sql = "SELECT " \
                                   "actual_reimbursement_amount,actual_total_pc  " \
                                   " FROM  rp_verification_info " \
                                   " where verification_code =  '%s' and recycle_id = '0' " % code

                bill_info = PyMysql.get_db_data(payment_sql) # 1.1 缴费工单查询
                if not bill_info:
                    bill_info = PyMysql.get_db_data(verification_sql) # 1.2 核销工单查询
                bill_info = bill_info[0]
                failed_result_list.append([code, audit_refused_type, stat_refused_type])
                insert_sql = """INSERT INTO audit_refused_info(code,audit_refused_type,stat_refused_type,amount,total_power,uuid) 
                                            values ('""" + code + """','""" + audit_refused_type + """','""" + stat_refused_type + """','""" + \
                             bill_info['actual_reimbursement_amount'] + """','""" + bill_info[
                                 'actual_total_pc'] + """','""" + audit_uuid + """')"""
                # print(insert_sql)
                PyMysql().execute_sql(insert_sql)
                PyMysql().update_nf_audit_info(code, '不通过', audit_refused_type)

    # 如果是通过或不通过情况，给nf_audit_info表该工单最新一条审核环节更新状态、时间、不通过类型
    @staticmethod
    def update_nf_audit_info(bill_number,audit_findings,audit_refused_type):
        # bill_number：单号，audit_findings：状态（通过/不通过），audit_refused_type：不通过类型
        etime = str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        update_sql = """update nf_audit_info set etime = '""" + etime + """',audit_findings = '""" + audit_findings + """',audit_refused_type = '""" + audit_refused_type + """' where code = '""" + bill_number + """' order by id desc limit 1"""
        # print(update_sql)
        PyMysql().execute_sql(update_sql)

    """
        生成指定位数的随机数
    """
    @staticmethod
    def generate_random_str(randomlength=16):
        """
        生成一个指定长度的随机字符串，其中
        string.digits=0123456789
        string.ascii_letters=abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
        """
        str_list = [random.choice(string.digits + string.ascii_letters) for i in range(randomlength)]
        random_str = ''.join(str_list)
        return random_str